"""
Case Type   : 行存压缩
Case Name   : 普通非压缩表，修改为压缩表，使用gs_basebackup备份
Create At   : 2023/8/10
Owner       : li-xin12345
Description :
    1、建普通表（非压缩表），并插入数据
    2、修改普通表为压缩表
    3、查看表占用磁盘大小、数据量等
    4、新建目录
    5、执行备份
    6、停止数据库
    7、使用备份目录启动数据库
    8、查询数据库状态确认是否启动成功
    9、查询测试表是否存在，查看表占用磁盘大小、数据量等，与备份前对比
    10、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、成功
    8、成功
    9、与备份前一致
    10、成功
History     :
    Modified by li-xin12345 at 2023/11/6:备机重建成功后，增加等待时间
"""

import os
import unittest
import time

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

Pri_U_SH = CommonSH('PrimaryDbUser')
Node_Num = Pri_U_SH.get_node_num()


@unittest.skipIf(3 > Node_Num, '不满足一主两备环境跳过')
class RowCompressTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.std1_sh = CommonSH('Standby1DbUser')
        self.std2_sh = CommonSH('Standby2DbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.tb_name = 't_alter_rowcompress_0062'
        self.backup_dir = 'f_aler_rowcompress_0062'
        self.backup_path = os.path.join(os.path.dirname(
            macro.DB_INSTANCE_PATH), self.backup_dir)
        self.alter_compress_param = 'compresstype=2, ' \
                                    'compress_chunk_size=1024, ' \
                                    'compress_prealloc_chunks=6, ' \
                                    'compress_byte_convert=true, ' \
                                    'compress_diff_convert=true, ' \
                                    'compress_level=6'

    def test_row_compress(self):
        text = '-----step1:建普通表（非压缩表），并插入数据;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name} cascade;
        create table {self.tb_name}(columnone integer, 
        columntwo char(50), columnthree varchar(50), columnfour integer, 
        columnfive char(50), columnsix varchar(50), columnseven char(50), 
        columneight char(50), columnnine varchar(50), columnten varchar(50), 
        columneleven char(50), columntwelve char(50), 
        columnthirteen varchar(50), columnfourteen char(50), 
        columnfifteem varchar(50));
        insert into {self.tb_name} values(generate_series(0, 1000),
        'test',substring(md5(random()::text), 1, 16), 2, 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16), 
        substring(md5(random()::text), 1, 16));
        '''
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      '执行失败：' + text)
        self.assertIn('INSERT 0 1001', sql_res, '执行失败：' + text)

        text = '-----step2:修改普通表为压缩表;expect:成功;expect:成功-----'
        self.log.info(text)
        sql = f'alter table {self.tb_name} set ' \
              f'({self.alter_compress_param}); checkpoint;' \
              f'\\d+ {self.tb_name}'
        sql_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.CHECKPOINT_SUCCESS_MSG, sql_res,
                      '执行失败：' + text)
        self.assertIn(f'{self.alter_compress_param}', sql_res,
                      '执行失败：' + text)

        text = '-----step3:查看表占用磁盘大小、数据量等-----'
        self.log.info(text)
        sql = f"select pg_size_pretty(pg_total_relation_size(" \
              f"'{self.tb_name}')) as table_size;"
        res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(res)
        self.assertNotIn('ERROR', res, '执行失败：' + text)

        sql = f'select checksum({self.tb_name}::text) from {self.tb_name};'
        before_size = Pri_U_SH.execut_db_sql(sql)
        self.log.info(before_size)
        self.assertNotIn('ERROR', before_size, '执行失败：' + text)

        text = '-----step4:新建目录-----'
        self.log.info(text)
        cmd = f"rm -rf {self.backup_path};mkdir -p {self.backup_path};" \
              f"chmod 700 {self.backup_path};" \
              f"ls {os.path.dirname(macro.DB_INSTANCE_PATH)} | " \
              f"grep '{self.backup_dir}';"
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_user, cmd)
        self.assertIn(f'{self.backup_dir}', res, '执行失败：' + text)

        self.log.info('-----step5:执行备份;expect:成功-----')
        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_basebackup -D {self.backup_path} -Fp -X stream " \
              f"-p {self.pri_user.db_port} -l gauss_042.bak " \
              f"-U {self.pri_user.ssh_user} -w"
        self.log.info(cmd)
        res = self.common.get_sh_result(self.pri_user, cmd)
        self.assertIn('base backup successfully', res, '执行失败：' + text)

        self.log.info('-----step6:停止数据库;expect:成功-----')
        stop_res = Pri_U_SH.stop_db_instance()
        self.assertIn(self.constant.GS_CTL_STOP_SUCCESS_MSG, stop_res,
                      '执行失败：' + text)

        self.log.info('-----step7:使用备份目录启动数据库;expect:成功-----')
        start_res = Pri_U_SH.start_db_instance(dn_path=self.backup_path)
        self.assertIn(self.constant.RESTART_SUCCESS_MSG, start_res,
                      '执行失败：' + text)

        self.log.info('-----step8:查询数据库状态确认是否启动成功;expect:成功-----')
        res1 = self.std1_sh.build_standby(mode='')
        self.assertIn(self.constant.BUILD_SUCCESS_MSG, res1, '备1build失败')
        res2 = self.std2_sh.build_standby(mode='')
        self.assertIn(self.constant.BUILD_SUCCESS_MSG, res2, '备2build失败')

        cmd = f"source {macro.DB_ENV_PATH};" \
              f"gs_ctl query -D {self.backup_path}"
        self.log.info(cmd)
        status = self.common.get_sh_result(self.pri_user, cmd)
        self.assertTrue('Normal' or 'Degraded' in status, '数据库状态异常')

        text = '-----step9:再次查看表占用磁盘大小、数据量等，与备份前对比;' \
               'expect:查看数据与备份前一致-----'
        self.log.info(text)
        sql = f"select pg_size_pretty(pg_total_relation_size(" \
              f"'{self.tb_name}')) as table_size;"
        res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(res)
        self.assertNotIn('ERROR', res, '执行失败：' + text)

        sql = f'select checksum({self.tb_name}::text) from {self.tb_name};'
        after_size = Pri_U_SH.execut_db_sql(sql)
        self.log.info(after_size)
        self.assertNotIn('ERROR', before_size, '执行失败：' + text)
        self.assertEqual(before_size, after_size, '执行失败：' + text)

    def tearDown(self):
        text = '-----step10:清理环境; expect:成功-----'
        self.log.info(text)
        stop_res = Pri_U_SH.stop_db_instance(dn_path=self.backup_path)
        self.log.info(stop_res)
        start_res = Pri_U_SH.start_db_instance()
        self.log.info(start_res)

        cmd = f'rm -rf {self.backup_path};' \
              f'if [ -d {self.backup_path} ]; ' \
              f'then echo "exists"; else echo "not exists"; fi'
        rm_res = self.common.get_sh_result(self.pri_user, cmd)
        self.log.info(rm_res)

        sql = f'drop table if exists {self.tb_name};'
        drop_res = Pri_U_SH.execut_db_sql(sql)
        self.log.info(drop_res)

        status = Pri_U_SH.get_db_cluster_status(param='status')
        self.log.info(status)
        if status is False:
            build_res1 = self.std1_sh.build_standby(mode='')
            self.assertIn(self.constant.BUILD_SUCCESS_MSG, build_res1)
            build_res2 = self.std2_sh.build_standby(mode='')
            self.assertIn(self.constant.BUILD_SUCCESS_MSG, build_res2)
            time.sleep(30)
            status = Pri_U_SH.get_db_cluster_status(param='status')
            self.log.info(status)

        self.assertTrue(status)
        self.assertIn(self.constant.GS_CTL_STOP_SUCCESS_MSG, stop_res,
                      '执行失败：' + text)
        self.assertIn(self.constant.RESTART_SUCCESS_MSG, start_res,
                      '执行失败：' + text)
        self.assertEqual('not exists', rm_res, '文件删除失败')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res,
                      '执行失败：' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
